﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using PortalLogReport.Models;
using PortalLogReport.Repositories;
using System.Data;
using PortalLogReport.Common;
using Vertica.Data.VerticaClient;
using System.Text;

namespace PortalLogReport.Repositories
{
    public class LevelRepository : IResultRepository
    {
        #region Repository Properties

        public KeyValuePair<ChartCommon.LevelTypes, string> LevelTypes { get; set; }
        public ChartCommon.Interval IntervalType { get; set; }
        public string Level1 { get; set; }
        public string Level2 { get; set; }

        #endregion;

        #region Constructor
        public LevelRepository(string level1, string level2)
        {
            //LevelTypes = new KeyValuePair<ChartCommon.LevelTypes, string>(
            //    (ChartCommon.LevelTypes)Enum.Parse(typeof(ChartCommon.LevelTypes), subDimensionType.Key), subDimensionType.Value);
            this.Level1 = level1;
            this.Level2 = level2;
        }

        #endregion

        #region Repository Methods

        public IList<BaseChartModel> GetResults(DateTime startDate, DateTime endDate)
        {
            return null;
        }

        public BaseChartModel GetResult(DateTime startDate, DateTime endDate)
        {


            StringBuilder commTextBuilder = new StringBuilder();
            commTextBuilder.AppendLine(@"select fre_interval, dimension , count(dimension) result from (");
            commTextBuilder.AppendLine(BuildSelectSQL());
            commTextBuilder.AppendLine("from public.PORTAL_LOG log ");
            commTextBuilder.AppendLine("where log.severity='Error'" + BuildWhereSQL() + " ) a ");
            if (!Level2.Equals("All"))
            {
                commTextBuilder.AppendLine(" where a.dimension != 'Others'");

            }

            commTextBuilder.AppendLine("group by fre_interval, dimension");

            VerticaParameter[] verticaParams = new VerticaParameter[2];
            verticaParams[0] = new VerticaParameter("startDate", VerticaType.DateTime, startDate);
            verticaParams[1] = new VerticaParameter("endDate", VerticaType.DateTime, endDate);

            DataSet ds = VerticalHelper.ExecuteDataset(CommandType.Text, commTextBuilder.ToString(), verticaParams);

            BaseChartModel baseChartModel = new BaseChartModel();

            Dictionary<string, Dictionary<string, int>> dataModel = new Dictionary<string, Dictionary<string, int>>();

            if (ds != null && ds.Tables.Count>0)
            {
                if (IntervalType == ChartCommon.Interval.None)
                {
                    dataModel = ds.Tables[0].AsEnumerable().OrderBy(x => x["fre_interval"]).OrderByDescending(x => x["result"]).GroupBy(x => x["fre_interval"].ToString()).
                                         ToDictionary(g => g.Key.ToString(), g => g.GroupBy(p => p["dimension"].ToString()).
                                             ToDictionary(x => x.Key.ToString(), x => Convert.ToInt32(x.First()["result"])));
                }
                else
                {
                    dataModel = ds.Tables[0].AsEnumerable().OrderBy(x => x["fre_interval"]).GroupBy(x => x["fre_interval"].ToString()).
                                     ToDictionary(g => g.Key.ToString(), g => g.GroupBy(p => p["dimension"].ToString()).
                                         ToDictionary(x => x.Key.ToString(), x => Convert.ToInt32(x.First()["result"])));
                }
            }

            return GetTop10Results(baseChartModel, dataModel);
        }

        private BaseChartModel GetTop10Results(BaseChartModel model, Dictionary<string, Dictionary<string, int>> dataModel)
        {
            model.BaseModel = new Dictionary<string, Dictionary<string, int>>();

            var enumerator = dataModel.GetEnumerator();
            while (enumerator.MoveNext())
            {
                var dataResults = enumerator.Current.Value.AsEnumerable().OrderByDescending(x => x.Value).Take(10).ToDictionary(k => k.Key, k => k.Value);
                model.BaseModel.Add(enumerator.Current.Key, dataResults);
            }

            return model;
        }

        private void GetTop10Results(BaseChartModel baseChartModel)
        {
            var enumerator = baseChartModel.BaseModel.GetEnumerator();
            if (enumerator.MoveNext())
            {
                //var max = from x in enumerator.Current.Value orderby x.Value select x;
                baseChartModel.BaseModel[enumerator.Current.Key] = enumerator.Current.Value.AsEnumerable().OrderByDescending(x => x.Value).Take(10).ToDictionary(k => k.Key, k => k.Value);
            }
        }

        public string BuildSelectSQL()
        {
            StringBuilder sqlString = new StringBuilder();

            sqlString.AppendLine(@"select l1.dim_1, l1.dim_1_txt, l2.dim, l2.dim_txt, l3.regex_nm, l3.regex_format");
            sqlString.AppendLine(" from level_1 l1 , level_2 l2, level_3 l3");
            sqlString.AppendLine(" where l1.l_1_id = l2.l_1_id and l2.l_2_id = l3.l_2_id");
            sqlString.AppendLine(@" and l1.dim_1 = @level1");

            VerticaParameter[] verticaParams;
            if (string.IsNullOrEmpty(Level2) || Level2.ToUpper().Equals("ALL"))
            {
                verticaParams = new VerticaParameter[1];
                verticaParams[0] = new VerticaParameter("level1", VerticaType.VarChar, Level1);
            }
            else
            {
                sqlString.AppendLine(@" and l2.dim = @level2");
                verticaParams = new VerticaParameter[2];
                verticaParams[0] = new VerticaParameter("level1", VerticaType.VarChar, Level1);
                verticaParams[1] = new VerticaParameter("level2", VerticaType.VarChar, Level2);
            }

            DataSet dsLevel2 = VerticalHelper.ExecuteDataset(CommandType.Text, sqlString.ToString(), verticaParams);

            var l2Dims = dsLevel2.Tables[0].AsEnumerable().Select(row =>
             new
             {
                 Dim = row.Field<string>("dim").ToString()
             }).
             Distinct().ToList();


            StringBuilder RegexSqlString = new StringBuilder();
            RegexSqlString.AppendLine(@" case ");


            foreach (var dim in l2Dims)
            {
                DataRow[] l3Dims = dsLevel2.Tables[0].AsEnumerable().Where(x => x["dim"].ToString() == dim.Dim).ToArray();

                foreach (DataRow dr in l3Dims)
                {
                    string dimension = Level2.Equals("All") ? dr["dim"].ToString() : dr["regex_nm"].ToString();
                    RegexSqlString.AppendLine(" when REGEXP_COUNT(" + Level1 + ", '" + dr["regex_format"].ToString() + "', 1 , 'i')=1 then '" + dimension + "'");
                }

                sqlString.Clear();
            }

            RegexSqlString.AppendLine(" else 'Others' end dimension ,");




            sqlString.Clear();
            sqlString.AppendLine("select " + RegexSqlString.ToString());

            switch (this.IntervalType)
            {
                case ChartCommon.Interval.Month:
                    sqlString.AppendLine("to_char(year(log.timestamp),'9999') || '/' || to_char(month(log.timestamp),'00') as fre_interval ");
                    break;
                case ChartCommon.Interval.Week:
                    sqlString.AppendLine("to_char(year(log.timestamp),'9999') || '/' || to_char(week(log.timestamp),'00') as fre_interval ");
                    break;
                case ChartCommon.Interval.Year:
                    sqlString.AppendLine("to_char(year(log.timestamp),'9999') as fre_interval ");
                    break;
                default:
                    sqlString.AppendLine("'' as fre_interval ");
                    break;
            }

            return sqlString.ToString();
        }

        public string BuildWhereSQL()
        {
            StringBuilder sqlString = new StringBuilder();

            sqlString.Append(" and timestamp>=@startDate and timestamp<@endDate");
            return sqlString.ToString();
        }

        #endregion
    }
}
